package com.qtummatrix.dao.impl;

import com.qtummatrix.dao.Uploading;
import com.qtummatrix.entity.ClientEntity;
import com.qtummatrix.utils.ReadExcel;
import jxl.read.biff.BiffException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * create by Gongshiyong  2019-11-21 19:39
 */

@Repository
public class UploadingImpl implements Uploading {


    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    @Qualifier("sessionFactory")
    public SessionFactory sessionFactory;

    public Session getSession() {
        return sessionFactory.getCurrentSession();
    }

    //打印答案信息跟客户信息对比
    @Override
    public List<Map<String, Object>> upMess() {

        ReadExcel readExcel = new ReadExcel();
        try {
            List<ClientEntity> hu = readExcel.hu();
            List<String> ka = new ArrayList<String>();
            List<String> la = new ArrayList<String>();
            String m1 = hu.get(1).getMid();
            String t1 = hu.get(1).getWId1();
            String d1 = hu.get(1).getIssue1();
            String t2 = hu.get(1).getWId2();
            String d2 = hu.get(1).getIssue2();
            String t3 = hu.get(1).getWId3();
            String d3 = hu.get(1).getIssue3();
            String t4 = hu.get(1).getWId4();
            String d4 = hu.get(1).getIssue4();
            String t5 = hu.get(1).getWId5();
            String d5 = hu.get(1).getIssue5();
            String t6 = hu.get(1).getWId6();
            String d6 = hu.get(1).getIssue6();
            String t7 = hu.get(1).getWId7();
            String d7 = hu.get(1).getIssue7();
            String t8 = hu.get(1).getWId8();
            String d8 = hu.get(1).getIssue8();
            String t9 = hu.get(1).getWId9();
            String d9 = hu.get(1).getIssue9();
            String t10 = hu.get(1).getWId10();
            String d10 = hu.get(1).getIssue10();
            la.add(t1);
            la.add(t2);
            la.add(t3);
            la.add(t4);
            la.add(t5);
            la.add(t6);
            la.add(t7);
            la.add(t8);
            la.add(t9);
            la.add(t10);
            ka.add(d1);
            ka.add(d2);
            ka.add(d3);
            ka.add(d4);
            ka.add(d5);
            ka.add(d6);
            ka.add(d7);
            ka.add(d8);
            ka.add(d9);
            ka.add(d10);
            List<Map<String, Object>> mapList = null;
            for (int i = 0; i < ka.size(); i++) {
                String sql = "select ? from questionsentity where id=?";
                mapList = jdbcTemplate.queryForList(sql, ka.get(i), la.get(i));
            }
            return mapList;
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 查询所有银行
     */
    @Override
    public List<Map<String, Object>> getAllBankName() {
        String sql = "select ID, BANKNAME from bankinfoentity";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }

    /**
     * 查询用户信息
     *
     * @param customer
     * @param bank
     * @return
     */
    @Override
    public List<Map<String, Object>> getInformation(String customer, String bank, int fiest, int rpws) {
        if (customer.equals("") && !bank.equals("")) {
            String sql1 = "select  c.id, c.CUSTOMERNAME ,b.BANKNAME ,c.Report,c.CREDITGRADE,c.CREDITBERIOD,c" +
                    ".CREDITLIMIT,c.Repayment FROM customerinfoentity as c LEFT JOIN bankinfoentity as b " +
                    "on c.BANKID =b.id " +
                    "LEFT JOIN  examine as e ON c.STATUS =e.STATUSID " +
                    "where b.BANKNAME =? limit " + fiest + "," + rpws;
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql1, bank);
            return maps;
        }
        if (bank.equals("") && !customer.equals("")) {
            String sql1 = "select  c.id, c.CUSTOMERNAME ,b.BANKNAME ,c.Report,c.CREDITGRADE,c.CREDITBERIOD,c" +
                    ".CREDITLIMIT,c.Repayment FROM customerinfoentity as c LEFT JOIN bankinfoentity as b " +
                    "on c.BANKID =b.id " +
                    "LEFT JOIN  examine as e ON c.STATUS =e.STATUSID " +
                    "where c.CUSTOMERNAME =? limit " + fiest + "," + rpws;
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql1, customer);
            return maps;
        }
        if (!customer.equals("") && !bank.equals("")) {
            String sql1 = "select  c.id, c.CUSTOMERNAME ,b.BANKNAME ,c.Report,c.CREDITGRADE,c.CREDITBERIOD,c" +
                    ".CREDITLIMIT,c.Repayment FROM customerinfoentity as c LEFT JOIN bankinfoentity as b " +
                    " on c.BANKID =b.id " +
                    " LEFT JOIN  examine as e ON c.STATUS =e.STATUSID " +
                    " where c.CUSTOMERNAME =? and  b.BANKNAME =? limit " + fiest + "," + rpws;
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql1, customer, bank);
            return maps;
        }
        if (customer.equals("") && bank.equals("")) {
            String sql1 = "select  c.id, c.CUSTOMERNAME ,b.BANKNAME ,c.Report,c.CREDITGRADE,c.CREDITBERIOD,c" +
                    ".CREDITLIMIT,c.Repayment FROM customerinfoentity as c LEFT JOIN bankinfoentity as b " +
                    "on c.BANKID =b.id " +
                    "LEFT JOIN  examine as e ON c.STATUS =e.STATUSID limit " + fiest + "," + rpws;
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql1);
            return mapList;
        }
        return null;
    }

    /**
     * 跳湖条
     *
     * @param customer
     * @param bank
     * @return
     */
    @Override
    public int miao(String customer, String bank) {
        if (customer.equals("") && bank.equals("")) {
            String sql1 = "select  c.id, c.CUSTOMERNAME ,b.BANKNAME ,c.Report FROM customerinfoentity as c LEFT JOIN " +
                    "bankinfoentity as b " +
                    "on c.BANKID =b.id " +
                    "LEFT JOIN  examine as e ON c.STATUS =e.STATUSID ";
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql1);
            return mapList.size();
        }
        return 0;
    }


    /**
     * 查询贷款产品
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> getProduct() {
        String sql = "select id, LOANNAME from loanproductsentity";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

    /**
     * 查询状态
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> getState() {
        String sql = "select StatusID,StatusName from examine";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }


    /**
     * 根据条件查询客户的基本信息
     */
    @Override
    public List<Map<String, Object>> getAccommodation(String snumbering, String snumbered, String scustomerName,
                                                      String sproduct, String sstate, String sRabobank, int fiest,
                                                      int rpws) {
        StringBuilder sql1 = new StringBuilder("SELECT y.LoanNumber, c.ID, c.CUSTOMERNAME, l.LOANNAME, b.Bankname,c" +
                ".Fen, c.CREDITGRADE, c.CREDITLIMIT, c.CREDITBERIOD, c.Repayment , e.StatusName FROM loans y " +
                "INNER JOIN loanproductsentity l ON y.cid = l.id INNER JOIN customerinfoentity c ON c.id = y.ID INNER" +
                " JOIN bankinfoentity b on y.BelongBankID = b.ID INNER JOIN examine e on y.StatusID = e.StatusID " +
                "where 1=1 ");
        //贷款编号
        if (!snumbering.equals("")) {
            sql1.append(" and  y.LoanNumber=" + snumbering);
        }
        //客户编号
        if (!snumbered.equals("")) {
            sql1.append(" and c.ID= " + snumbered);
        }
        //客户名称
        if (!scustomerName.equals("")) {
            sql1.append(" and c.CUSTOMERNAME=" + "'" + scustomerName + "'");
        }
        //产品名称
        if (!sproduct.equals("")) {
            sql1.append(" and l.LOANNAME=" + "'" + sproduct + "'");
        }
        //状态
        if (!sstate.equals("")) {
            sql1.append(" and e.StatusName=" + "'" + sstate + "'");
        }
        //所属银行
        if (!sRabobank.equals("")) {
            sql1.append(" and b.Bankname=" + "'" + sRabobank + "'");
        }
        if (1 == 1) {
            sql1.append(" limit " + fiest + "," + rpws);
        }
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql1.toString());
        return mapList;
    }

    /**
     * 获取用户信息
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> GettingInformation(int id) {
        String sql = "SELECT c.ID, c.CUSTOMERNAME, c.UID, c.GENDER, c.Marriage, c.ADDRESS, c.ADDRESSdan, c" +
                ".CUSTOMERWORK, c.Phone, c.ACCUMULATION, " +
                "c.MONTHLYINCOME, c.Education, b.Bankname, c.CREDITGRADE, " +
                "c.CREDITLIMIT FROM customerinfoentity c INNER JOIN bankinfoentity b ON b.ID=c.BANKID where c.ID=? ";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql, id);
        return mapList;
    }

    /**
     * 查询条数
     *
     * @param snumbering
     * @param snumbered
     * @param scustomerName
     * @param sproduct
     * @param sstate
     * @param sRabobank
     * @return
     */
    @Override
    public int tiao(String snumbering, String snumbered, String scustomerName, String sproduct, String sstate,
                    String sRabobank) {
        StringBuilder sql1 = new StringBuilder("SELECT y.LoanNumber, c.ID, c.CUSTOMERNAME, l.LOANNAME, b.Bankname,c" +
                ".Fen, c.CREDITGRADE, c.CREDITLIMIT, c.CREDITBERIOD, c.Repayment , e.StatusName FROM loans y " +
                "INNER JOIN loanproductsentity l ON y.cid = l.id INNER JOIN customerinfoentity c ON c.id = y.ID INNER" +
                " JOIN bankinfoentity b on y.BelongBankID = b.ID INNER JOIN examine e on y.StatusID = e.StatusID " +
                "where 1=1 ");
        //贷款编号
        if (!snumbering.equals("")) {
            sql1.append(" and  y.LoanNumber=" + snumbering);
        }
        //客户编号
        if (!snumbered.equals("")) {
            sql1.append(" and c.ID= " + snumbered);
        }
        //客户名称
        if (!scustomerName.equals("")) {
            sql1.append(" and c.CUSTOMERNAME=" + "'" + scustomerName + "'");
        }
        //产品名称
        if (!sproduct.equals("")) {
            sql1.append(" and l.LOANNAME=" + "'" + sproduct + "'");
        }
        //状态
        if (!sstate.equals("")) {
            sql1.append(" and e.StatusName=" + "'" + sstate + "'");
        }
        //所属银行
        if (!sRabobank.equals("")) {
            sql1.append(" and b.Bankname=" + "'" + sRabobank + "'");
        }
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql1.toString());
        return mapList.size();
    }


}